Release 10.1A: OpenEdge Data Management:
SQL Reference

FROM clause

Specifies one or more table references. Each table reference resolves to one table (either a table stored in the database or a virtual table resulting from processing the table reference) whose rows the query expression uses to create the result table.

Syntax

FROM table_ref [, table_ref ] ... [ { NO REORDER } ]

table_ref

There are three forms of table references:

A direct reference to a table, view, or synonym.
A derived table specified by a query expression in the FROM clause.
A joined table that combines rows and columns from multiple tables.

If there are multiple table references, SQL joins the tables to form an intermediate result table that is used as the basis for evaluating all other clauses in the query expression. That intermediate result table is the Cartesian product of rows in the tables in the FROM clause, formed by concatenating every row of every table with all other rows in all tables, as shown in the following syntax:

Syntax

table_name [ AS ] [ alias [ ( column_alias [ ... ] ) ] ] | ( query_expression ) [ AS ] alias [ (column_alias [ ... ] ) ] | [ ( ] joined_table [ ) ]

FROM table_name [ AS ] [ alias [ ( column_alias [ ... ] ) ] ]

Explicitly names a table. The name can be a table name, a view name, or a synonym.

alias

A name used to qualify column names in other parts of the query expression. Aliases are also called correlation names.

If you specify an alias, you must use it, and not the table name, to qualify column names that refer to the table. Query expressions that join a table with itself must use aliases to distinguish between references to column names.

Similar to table aliases, the column_alias provides an alternative name to use in column references elsewhere in the query expression. If you specify column aliases, you must specify them for all the columns in table_name. Also, if you specify column aliases in the FROM clause, you must use them, and not the column names, in references to the columns.

FROM ( query_expression ) [ AS ] [ alias [ ( column_alias [ ... ] ) ] ]

Specifies a derived table through a query expression. With derived tables, you must specify an alias to identify the derived table.

Derived tables can also specify column aliases. Column aliases provide alternative names to use in column references elsewhere in the query expression. If you specify column aliases, you must specify them for all the columns in the result table of the query expression. Also, if you specify column aliases in the FROM clause, you must use them, and not the column names, in references to the columns.

FROM [ ( ] joined_table [ ) ]

Combines data from two table references by specifying a join condition, as shown in the following syntax:

Syntax

{ table_ref CROSS JOIN table_ref | table_ref [ INNER | LEFT [ OUTER ] ] JOIN table_ref ON search_condition }

The syntax currently allowed in the FROM clause supports only a subset of possible join conditions:

CROSS JOIN specifies a Cartesian product of rows in the two tables. Every row in one table is joined to every row in the other table.
INNER JOIN specifies an inner join using the supplied search condition.
LEFT OUTER JOIN specifies a left outer join using the supplied search condition.
LEFT JOIN specifies the same conditions as an inner join.

You can also specify these and other join conditions in the WHERE clause of a query expression.

{ NO REORDER }

Disables join order optimization for the FROM clause. Use NO REORDER when you choose to override the join order chosen by the optimizer. The braces are part of the syntax for this optional clause.

Example

For customers with orders, retrieve their names and order info, as shown in the following example:

SELECT Customer.CustNum,Customer.Name, Order.OrderNum,Order.OrderDate FROM Customer, Order WHERE Customer.CustNum = Order.CustNum;